/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;


import Entity.Prestasi;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletRequest;
import library.Koneksi;

/**
 *
 * @author user
 */
public class DaftarPrestasi {
    
    Prestasi Prestasi;

    
    //method untuk menambah prestasi
    public boolean tambahPrestasi(Prestasi Prestasi){
        boolean hasil = false;
        this.Prestasi = Prestasi;
        Koneksi dbConn = new Koneksi();
        String sql = "insert into prestasi(ID_USER, NAMA_KEG, TAHUN_PRESTASI, PRESTASI_CAPAIAN, TINGKAT, BUKTI_PRESTASI, STATUS_PRESTASI)"
                + " value("
                 
                + "'"+this.Prestasi.getIdUser()+"',"
                + "'"+this.Prestasi.getNamaKegiatan()+"',"
                + "'"+this.Prestasi.getTahunPrestasi()+"',"
                + "'"+this.Prestasi.getPrestasiDicapai()+"',"
                + "'"+this.Prestasi.getTingkat()+"',"
                + "'"+this.Prestasi.getBuktiPrestasi()+"',"
                + "'"+this.Prestasi.getStatusPrestasi()+"'"
                + ")";
        System.out.println("sql : "+sql);
        PreparedStatement st = null;
        try {
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if(rs > 0){
                hasil = true;
            } else{
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("error : "+ex.getErrorCode());
            System.out.println("error : "+ex.getMessage());
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;
    }
    
     //method untuk mengambil data daftar prestasi
    public List<Prestasi> ambilDaftarPrestasi(){
        List<Prestasi> daftarPrestasi = new ArrayList<Prestasi>();
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi";
        
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
             rs = st.executeQuery();

            while(rs.next()){
                this.Prestasi = new Prestasi();
                Prestasi Prestasi = new Prestasi();
                Prestasi.setIdUser(rs.getString("ID_USER"));
                Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
                Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
                Prestasi.setTingkat(rs.getString("TINGKAT"));
                Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));
                daftarPrestasi.add(Prestasi);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPrestasi;
    }
    
    public List<Prestasi> ambilRekapPrestasi(){
        List<Prestasi> daftarPrestasi = new ArrayList<Prestasi>();
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi where STATUS_PRESTASI = '1'";
        
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                this.Prestasi = new Prestasi();
                Prestasi Prestasi = new Prestasi();
                Prestasi.setIdUser(rs.getString("ID_USER"));
                Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
                Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
                Prestasi.setTingkat(rs.getString("TINGKAT"));
                Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));
                daftarPrestasi.add(Prestasi);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPrestasi;
    }
        
    //method untuk menghapus data prestasi
    public boolean hapusPrestasi(Prestasi prestasi){

        boolean hasil = true;

        PreparedStatement st = null;
        Koneksi dbConn = new Koneksi();

        String sql = "DELETE FROM prestasi WHERE ID_PRESTASI = "+prestasi.getIdPrestasi();

        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if(rs > 0){
                hasil = true;
            } else{
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }
    
    
        //method untuk mengambil data beasiswa dengan id tertentu
    public Prestasi ambilDataPrestasi(String idPrestasi){

        Koneksi dbConn = new Koneksi();
        String sql = "SELECT * FROM prestasi as A, prestasi as B where A.ID_PRESTASI = B.ID_PRESTASI and A.ID_PRESTASI="+idPrestasi;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                this.Prestasi = new Prestasi();
                Prestasi.setIdPrestasi(rs.getInt("ID_PRESTASI"));
                Prestasi.setIdUser(rs.getString("ID_USER"));
                Prestasi.setNamaKegiatan(rs.getString("NAMA_KEG"));
               Prestasi.setTahunPrestasi(rs.getString("TAHUN_PRESTASI"));
               Prestasi.setTingkat(rs.getString("TINGKAT"));
                 Prestasi.setPrestasiDicapai(rs.getString("PRESTASI_CAPAIAN"));
                Prestasi.setBuktiPrestasi(rs.getString("BUKTI_PRESTASI"));
                Prestasi.setStatusPrestasi(rs.getString("STATUS_PRESTASI"));
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return Prestasi;
        
      }

   //   method untuk mengedit beasiswa
    public boolean editPrestasi(Prestasi prestasi){
        boolean hasil = false;
        this.Prestasi = prestasi;
        Koneksi dbConn = new Koneksi();

        String sql = "update prestasi SET "
               +"NAMA_KEG = '"+this.Prestasi.getNamaKegiatan()+
                "', TAHUN_PRESTASI = '"+this.Prestasi.getTahunPrestasi()+
                "', PRESTASI_CAPAIAN = '"+this.Prestasi.getPrestasiDicapai()+
                "', TINGKAT = '"+this.Prestasi.getTingkat()+
                "', BUKTI_PRESTASI = '"+this.Prestasi.getBuktiPrestasi()+
                "', STATUS_PRESTASI = '"+this.Prestasi.getStatusPrestasi()+
                 "' where ID_PRESTASI = "+this.Prestasi.getIdPrestasi();
                
                
               
        PreparedStatement st = null;

        try {
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if(rs > 0){
                hasil = true;
            } else{
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPrestasi.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;
    }
    
}
